package com.seek.inter;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.seek.common.handler.SpringContextHolder;
import com.seek.model.dto.ResultInfo;
import com.seek.service.ReadService;
import com.seek.submail.utils.StringUtil;
import com.seek.util.CommonUtil;
import com.seek.util.ExportExcel;
import com.seek.util.UUIDUtil;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 报表输出相关接口
 *
 * Created by weh on 2017/7/31.
 */
public class ExcelExportInter {

  private static final Logger logger = LoggerFactory.getLogger(ExcelExportInter.class);

  private ReadService readService = (ReadService) SpringContextHolder.getBean("readService");


  /**
   * 已收款查询excel导出
   */
  public ResultInfo exportLoanOfRepayment(HttpServletRequest request, JSONObject reqJson)
      throws Exception {
    ResultInfo result = new ResultInfo();
    Long companyId = reqJson.getLong("companyId");
    String custPhone = reqJson.getString("custPhone");
    String rqq = reqJson.getString("rqq");
    String rqz = reqJson.getString("rqz");
    String repayStatus = reqJson.getString("repayStatus");
    String payType = reqJson.getString("payType");

    // 拼接检索条件
    StringBuffer where = new StringBuffer("");
    List<Object> params = new ArrayList<Object>();
    params.add(companyId);
    if (!CommonUtil.isEmpty(custPhone)) {
      custPhone = "%" + custPhone + "%";
      where.append(" and b.phone_no like ?");
      params.add(custPhone);
    }
    
    if (CommonUtil.getIntValue(repayStatus)==2) {
		if (!CommonUtil.isEmpty(rqq)) {
			where.append(" and a.create_time > ?");
			params.add(rqq);
		}
		if (!CommonUtil.isEmpty(rqz) && rqz.length()>=10) {
			rqz = rqz.substring(0, 10) + " 23:59:59";
			where.append(" and a.create_time < ?");
			params.add(rqz);
		}
	}else{
		if (!CommonUtil.isEmpty(rqq)) {
			where.append(" and a.pay_time > ?");
			params.add(rqq);
		}
		if (!CommonUtil.isEmpty(rqz) && rqz.length()>=10) {
			rqz = rqz.substring(0, 10) + " 23:59:59";
			where.append(" and a.pay_time < ?");
			params.add(rqz);
		}
	}
    
    if (CommonUtil.getIntValue(repayStatus)>0) {
      where.append(" and a.pay_status = ?");
      params.add(CommonUtil.getIntValue(repayStatus));
    }
    if (!CommonUtil.isEmpty(payType)) {
      where.append(" and a.pay_type = ?");
      params.add(CommonUtil.getIntValue(payType));
    }

    // 列表数据SQL: 把最需要审核的放在前面
    StringBuffer sb = new StringBuffer(
        "select *, case type when '还款' then payMoney - latefee else 0  ")
        .append(
            "end as balance, case type when '续借手续费' then payMoney - latefee else 0 end as interest ")
        .append("from (select a.id repayId,");
    if (CommonUtil.getIntValue(reqJson.getString("hidePhone")) > 0) {
      sb.append("REPLACE(b.phone_no, SUBSTR(b.phone_no,4,4), '****') ");
    } else {
      sb.append("b.phone_no");
    }
    sb.append(" custPhone,b.name custName,a.pay_no repayNo,")
        .append("convert(ifnull(a.pay_money,0)/100,DECIMAL(15,2)) payMoney, ")
        .append("convert(ifnull(a.latefee,0)/100,DECIMAL(15,2)) latefee,")
        .append("DATE_FORMAT(pay_time,'%Y-%m-%d %H:%i:%s')  payTime,case a.type when 1 then ")
        .append("'续借手续费' when 2 then '还款' end as type, case a.pay_type when 1 then ")
        .append("'支付宝还款' when 2 then '银行卡还款' when 3 then '手工还款' end as payType,")
        .append("case  when a.pay_status =1 then '成功' else '失败' end as payStatus,")
        .append("DATE_FORMAT(c.start_time,'%Y-%m-%d')  startTime,")
        .append("DATE_FORMAT(c.end_time,'%Y-%m-%d')  endTime")
        .append(" from t_loan_repayment a,t_customer b,t_loan c where a.loan_id=c.id ")
        .append("and c.customer_id=b.id and a.company_id= ?").append(where)
        .append(" order by a.id asc) d");

    List<Map<String, Object>> datas = readService.findMapBySql(sb.toString(), params.toArray());
    result.setStatus(0);
    result.setMessage("成功");
    if (CollectionUtils.isNotEmpty(datas)) {
      String[] titles = new String[]{"客户手机号", "客户姓名", "收款单号", "收款金额（元）",
          "滞纳金（元）", "本金（元）", "利息（元）", "业务类型", "支付方式", "支付时间", "贷款开始时间", "贷款结束时间"};
      String[] fields = new String[]{"custPhone", "custName", "repayNo", "payMoney",
          "latefee", "balance", "interest", "type", "payType", "payTime", "startTime", "endTime"};
      String url = ExportExcel
          .exportExcel(UUIDUtil.getUUID() + ".xls", "收款对账", titles, fields, datas);
      result.setBody(url);
    }

    return result;
  }


  /**
   * 获取自动放款成功excel
   */
  public ResultInfo exportLoanDownList(HttpServletRequest request, JSONObject reqJson)
      throws Exception {
    ResultInfo result = new ResultInfo();
    Long companyId = reqJson.getLong("companyId");
    String custPhone = reqJson.getString("custPhone");
    String rqq = reqJson.getString("rqq");
    String rqz = reqJson.getString("rqz");
    int downType = CommonUtil.getIntValue(reqJson.getLong("downType"));
    int downStatus = CommonUtil.getIntValue(reqJson.getInteger("downStatus"));
    int loanType = CommonUtil.getIntValue(reqJson.getInteger("loanType"));
    // 拼接检索条件
    StringBuffer where = new StringBuffer("");
    List<Object> params = new ArrayList<Object>();
    params.add(companyId);
    if (!CommonUtil.isEmpty(custPhone)) {
      custPhone = "%" + custPhone + "%";
      where.append(" and b.phone_no like ?");
      params.add(custPhone);
    }
    if (!CommonUtil.isEmpty(rqq)) {
      where.append(" and a.down_time > ?");
      params.add(rqq);
    }
    if (!CommonUtil.isEmpty(rqz)) {
      rqz = rqz.substring(0, 10) + " 23:59:59";
      where.append(" and a.down_time < ?");
      params.add(rqz);
    }
    if (downType > 0) {
      where.append(" and a.down_type= ?");
      params.add(downType);
    }
    if (downStatus > 0) {
      if (downStatus == 1) {
        where.append(" and a.loan_status=2");
      } else if (downStatus == 2) {
        where.append(" and a.loan_status in (3,5,6)");
      } else {
        where.append(" and a.loan_status=4");
      }
    } else {
      where.append(" and a.loan_status>2");
    }

    if (loanType > 0) {
      where.append(" and a.loan_type= ?");
      params.add(loanType);
    }

    StringBuffer sb = new StringBuffer("select a.id loanId,");
    if (CommonUtil.getIntValue(reqJson.getString("hidePhone")) > 0) {
      sb.append(
          "REPLACE(b.phone_no, SUBSTR(b.phone_no,4,4), '****')");
    } else {
      sb.append("b.phone_no");
    }
    sb.append(
        " custPhone,b.name custName,convert(ifnull(a.req_money,0)/100,DECIMAL(15,2)) reqMoney,")
        .append("DATE_FORMAT(a.start_time,'%Y-%m-%d') startTime,")
        .append(
            "DATE_FORMAT(a.end_time,'%Y-%m-%d') endTime,convert(ifnull(a.down_money,0)/100,DECIMAL(15,2)) ")
        .append("downMoney,DATE_FORMAT(a.req_date,'%Y-%m-%d %H:%i:%s') reqTime, CASE a.loan_type ")
        .append("WHEN 1 THEN '借款'  WHEN 2 THEN '续借' end as loanType,")
        .append("CASE a.loan_status WHEN 0 THEN '未审核' WHEN 1 THEN '审核未通过' WHEN 2 THEN ")
        .append("'审核通过' WHEN 3 THEN'已放款' WHEN 4 THEN '放款失败' WHEN 5 THEN '已还款' WHEN 6 ")
        .append("THEN '坏帐' end as loanStatus,CASE a.down_type WHEN 1 THEN '自动放款' WHEN 2 THEN ")
        .append("'手工放款' end as downType,a.down_remark downRemark,c.bank_name bankName, ")
        .append(" c.card_number cardNumber,DATE_FORMAT(a.down_time,'%Y-%m-%d %H:%i:%s') downTime,")
        .append("a.down_img downImg,b.id custId from t_loan a, t_customer b, t_customer_card c")
        .append(
            " where c.history=0 and a.customer_id=b.id and b.id=c.customer_id and a.company_id=? ");
    sb.append(where).append(" order by a.id asc");

    List<Map<String, Object>> datas = readService.findMapBySql(sb.toString(), params.toArray());
    result.setStatus(0);
    result.setMessage("成功");
    if (CollectionUtils.isNotEmpty(datas)) {
      String[] titles = new String[]{"客户手机号", "客户姓名", "贷款金额（元）",
          "放款金额（元）","下款类型", "开始时间", "结束时间", "申请贷款时间", "货款状态", "下款方式",
          "下款备注", "银行名称", "银行账号", "下款时间"};
      String[] fields = new String[]{"custPhone", "custName", "reqMoney", "downMoney","loanType",
          "startTime", "endTime", "reqTime", "loanStatus", "downType", "downRemark", "bankName",
          "cardNumber", "downTime"};

      String url = ExportExcel
          .exportExcel(UUIDUtil.getUUID() + ".xls", "放款对账", titles, fields, datas);
      result.setBody(url);
    }
    return result;
  }



  /**
   * 导出待催收分配列表
   * @param request
   * @return
   * @throws Exception
   */
  public ResultInfo exportOverdueLoanList(HttpServletRequest request, JSONObject reqJson) throws Exception {
    ResultInfo result = new ResultInfo();
    Long companyId = reqJson.getLong("companyId");
    String custPhone = reqJson.getString("custPhone");
    String rqq = reqJson.getString("rqq");
    String rqz = reqJson.getString("rqz");
    int delayDays = CommonUtil.getIntValue(reqJson.getString("delayDays"));
    int overdueSpan = CommonUtil.getIntValue(reqJson.getString("overdueSpan"));
    int excludeBad = CommonUtil.getIntValue(reqJson.getString("excludeBad"));
    String overduerId = reqJson.getString("overduerId");

    // 拼接检索条件
    StringBuffer where = new StringBuffer("");
    List<Object> params = new ArrayList<Object>();
    params.add(companyId);
    if (!CommonUtil.isEmpty(custPhone)){
      custPhone = "%"+custPhone+"%";
      where.append(" and b.phone_no like ?");
      params.add(custPhone);
    }
    if (!CommonUtil.isEmpty(rqq)){
      where.append(" and a.end_time > ?");
      params.add(rqq);
    }
    if (!CommonUtil.isEmpty(rqz)){
      rqz = rqz.substring(0,10)+" 23:59:59";
      where.append(" and a.end_time <= ?");
      params.add(rqz);
    }
    if (delayDays > 0){
      where.append(" and datediff(now(),a.end_time)>= ?");
      params.add(delayDays);
    }
    if (overdueSpan > 0){
      where.append(" and datediff(now(),ifnull(a.last_overdue_time,a.end_time))>= ?");
      params.add(delayDays);
    }
    if (excludeBad > 0){
      where.append(" and a.loan_status=3");
    }

    if(!StringUtils.isEmpty(overduerId)){
      if (CommonUtil.getIntValue(overduerId) == 0){
        where.append(" and a.overduer_id is null");
      }else if (CommonUtil.getIntValue(overduerId) > 0){
        where.append(" and a.overduer_id=?");
        params.add(CommonUtil.getIntValue(overduerId));
      }
    }
    // 列表数据SQL: 把最需要审核的放在前面
    StringBuffer sb = new StringBuffer("");
    if (CommonUtil.getIntValue(reqJson.getString("hidePhone"))>0)
      sb.append("select a.id loanId,REPLACE(b.phone_no, SUBSTR(b.phone_no,4,4), '****') custPhone,b.name custName,convert(ifnull(a.req_money,0)/100,DECIMAL(15,2)) reqMoney,DATE_FORMAT(a.start_time,'%Y-%m-%d') startTime,");
    else
      sb.append("select a.id loanId,b.phone_no custPhone,b.name custName,convert(ifnull(a.req_money,0)/100,DECIMAL(15,2))  reqMoney,DATE_FORMAT(a.start_time,'%Y-%m-%d') startTime,");
    sb.append(" DATE_FORMAT(a.end_time,'%Y-%m-%d') endTime,a.req_date reqTime,(case when a.loan_status=6 then '坏帐' else '非坏帐' end) isBad,")
        .append(" datediff(now(),a.end_time) delayDays,convert(ifnull(a.late_fee - a.off_latefee,0)/100,DECIMAL(15,2)) lateFee,a.overduer_id overduerId,")
        .append(" (select u.user_name from t_sys_user u where a.overduer_id=u.id) overduerName,DATE_FORMAT(a.last_overdue_time,'%Y-%m-%d %H:%i:%s')  lastOverdueTime,")
        .append(" (select count(1) from t_loan_overdue_log u where u.loan_id=a.id) overdueLogNum,b.id custId")
        .append(" from t_loan a, t_customer b where a.customer_id=b.id and a.company_id=? and a.loan_status in (3,6)");
    sb.append(where).append(" order by a.end_time,b.name  asc");

    result.setStatus(0);
    result.setMessage("成功");
    List<Map<String, Object>> datas = readService.findMapBySql(sb.toString(), params.toArray());
    if (CollectionUtils.isNotEmpty(datas)) {
      String[] titles = new String[]{"催收人","客户手机号", "客户姓名", "贷款金额（元）", "开始时间",
          "结束时间", "是否坏帐", "逾期天数", "滞纳金（元）", "最近催收日期", "催收次数"};
      String[] fields = new String[]{"overduerName","custPhone", "custName", "reqMoney", "startTime", "endTime",
          "isBad", "delayDays", "lateFee",  "lastOverdueTime","overdueLogNum"};

      String url = ExportExcel
          .exportExcel(UUIDUtil.getUUID() + ".xls", "催收列表", titles, fields, datas);
      result.setBody(url);
    }
    return result;
  }

  /**
   * 应收款列表导出
   *
   * @param request
   * @return
   * @throws Exception
   */
  public ResultInfo exportLoanOfReceivables(HttpServletRequest request, JSONObject reqJson) throws Exception {
    ResultInfo result = new ResultInfo();
    Long companyId = reqJson.getLong("companyId");
    String custPhone = reqJson.getString("custPhone");
    String rqq = reqJson.getString("rqq");
    String rqz = reqJson.getString("rqz");
    int delayDays = CommonUtil.getIntValue(reqJson.getLong("delayDays"));
    int excludeBad = CommonUtil.getIntValue(reqJson.getLong("excludeBad"));
    int excludeToday = CommonUtil.getIntValue(reqJson.getLong("excludeToday"));

    // 拼接检索条件
    StringBuffer where = new StringBuffer("");
    List<Object> params = new ArrayList<Object>();
    params.add(companyId);
    if (!CommonUtil.isEmpty(custPhone)) {
      custPhone = "%" + custPhone + "%";
      where.append(" and b.phone_no like ?");
      params.add(custPhone);
    }
    if (!CommonUtil.isEmpty(rqq)) {
      where.append(" and a.end_time > ?");
      params.add(rqq);
    }
    if (!CommonUtil.isEmpty(rqz)) {
      rqz = rqz.substring(0, 10) + " 23:59:59";
      where.append(" and a.end_time <= ?");
      params.add(rqz);
    }
    if (delayDays > 0) {
      where.append(" and datediff(now(),a.end_time)>= ?");
      params.add(delayDays);
    }

    //excludeToday 剔除当日放款及还款数据
    if (excludeToday > 0) {
      if (excludeBad > 0) {
        where.append(" and (a.loan_status=3 or (a.loan_status = 5 and DATE_FORMAT(a.payback_date,'%Y-%m-%d')=DATE_FORMAT(now(),'%Y-%m-%d')))");
      }else{
        where.append(" and (a.loan_status in (3,6) or (a.loan_status = 5 and DATE_FORMAT(a.payback_date,'%Y-%m-%d')=DATE_FORMAT(now(),'%Y-%m-%d')))");
      }
      where.append(" and DATE_FORMAT(a.down_time,'%Y-%m-%d') !=DATE_FORMAT(now(),'%Y-%m-%d')");
    }else{

      if (excludeBad > 0) {
        where.append(" and a.loan_status=3");
      }else{
        where.append(" and a.loan_status in (3,6)");
      }

    }

    StringBuffer sb = new StringBuffer("");
    if (CommonUtil.getIntValue(reqJson.getString("hidePhone")) > 0)
      sb.append(
          "select a.id loanId,REPLACE(b.phone_no, SUBSTR(b.phone_no,4,4), '****') custPhone,b.name custName,convert(ifnull(a.req_money,0)/100,DECIMAL(15,2)) reqMoney,DATE_FORMAT(a.start_time,'%Y-%m-%d') startTime,");
    else
      sb.append(
          "select a.id loanId,b.phone_no custPhone,b.name custName,convert(ifnull(a.req_money,0)/100,DECIMAL(15,2)) reqMoney,DATE_FORMAT(a.start_time,'%Y-%m-%d') startTime,");
    sb.append(" DATE_FORMAT(a.end_time,'%Y-%m-%d') endTime,a.req_date reqTime,(case when a.loan_status=6 then '坏帐' else '非坏帐' end) isBad,")
        .append(" datediff(now(),a.end_time) delayDays,convert(ifnull(a.late_fee - a.off_latefee,0)/100,DECIMAL(15,2)) lateFee,")
        .append(" (select u.user_name from t_sys_user u where a.overduer_id=u.id) overduerName,a.last_overdue_time lastOverdueTime,")
        .append(" (case when a.auditor_id=-1 then '系统审核' else (select u.user_name from t_sys_user u where a.auditor_id=u.id) end) auditName")
        .append(" from t_loan a, t_customer b where a.customer_id=b.id and a.company_id=?");
    sb.append(where).append(" order by a.id asc");

    List<Map<String, Object>> datas = readService.findMapBySql(sb.toString(), params.toArray());
    result.setStatus(0);
    result.setMessage("成功");
    if (CollectionUtils.isNotEmpty(datas)) {
      String[] titles = new String[]{"客户手机号", "客户姓名", "贷款金额（元）", "开始时间",
          "结束时间", "是否坏帐", "逾期天数", "滞纳金（元）", "审核人"};
      String[] fields = new String[]{"custPhone", "custName", "reqMoney", "startTime", "endTime",
          "isBad", "delayDays", "lateFee",  "auditName"};

      String url = ExportExcel
          .exportExcel(UUIDUtil.getUUID() + ".xls", "应收款列表", titles, fields, datas);
      result.setBody(url);
    }
    return result;
  }

  /**
   * 坏帐导出
   *
   * @param request
   * @return
   * @throws Exception
   */
  public ResultInfo exportLoanOfBad(HttpServletRequest request, JSONObject reqJson) throws Exception {
    ResultInfo result = new ResultInfo();
    Long companyId = reqJson.getLong("companyId");
    String custPhone = reqJson.getString("custPhone");
    String rqq = reqJson.getString("rqq");
    String rqz = reqJson.getString("rqz");

    // 拼接检索条件
    StringBuffer where = new StringBuffer("");
    List<Object> params = new ArrayList<Object>();
    params.add(companyId);
    if (!CommonUtil.isEmpty(custPhone)) {
      custPhone = "%" + custPhone + "%";
      where.append(" and b.phone_no like ?");
      params.add(custPhone);
    }
    if (!CommonUtil.isEmpty(rqq)) {
      where.append(" and a.end_time > ?");
      params.add(rqq);
    }
    if (!CommonUtil.isEmpty(rqz)) {
      rqz = rqz.substring(0, 10) + " 23:59:59";
      where.append(" and a.end_time <= ?");
      params.add(rqz);
    }

    StringBuffer sb = new StringBuffer("");
    if (CommonUtil.getIntValue(reqJson.getString("hidePhone")) > 0)
      sb.append(
          "select a.id loanId,REPLACE(b.phone_no, SUBSTR(b.phone_no,4,4), '****') custPhone,b.name custName,convert(ifnull(a.req_money,0)/100,DECIMAL(15,2)) reqMoney,DATE_FORMAT(a.start_time,'%Y-%m-%d') startTime,");
    else
      sb.append(
          "select a.id loanId,b.phone_no custPhone,b.name custName,convert(ifnull(a.req_money,0)/100,DECIMAL(15,2)) reqMoney,DATE_FORMAT(a.start_time,'%Y-%m-%d') startTime,");
    sb.append(" DATE_FORMAT(a.end_time,'%Y-%m-%d') endTime,a.req_date reqTime,(case when a.loan_status=6 then '是' else '否' end) isBad,")
        .append(" datediff(now(),a.end_time) delayDays,convert(ifnull(a.late_fee - a.off_latefee,0)/100,DECIMAL(15,2)) lateFee,")
        .append(" (select u.user_name from t_sys_user u where a.overduer_id=u.id) overduerName,DATE_FORMAT(a.last_overdue_time,'%Y-%m-%d') lastOverdueTime,")
        .append(" (case when a.auditor_id=-1 then '系统审核' else (select u.user_name from t_sys_user u where a.auditor_id=u.id) end) auditName,")
        .append(" (select count(1) from t_loan_overdue_log u where u.loan_id=a.id) overdueLogNum")
        .append(" from t_loan a, t_customer b where a.customer_id=b.id and a.company_id=? and a.loan_status=6");
    sb.append(where).append(" order by a.id asc");

    List<Map<String, Object>> datas = readService.findMapBySql(sb.toString(), params.toArray());
    result.setStatus(0);
    result.setMessage("成功");
    if (CollectionUtils.isNotEmpty(datas)) {
      String[] titles = new String[]{"客户手机号", "客户姓名", "贷款金额（元）", "开始日期",
          "结束日期", "坏帐", "逾期天数", "应还滞纳金（元）", "审核人","催收人","最近催收日期","催收次数"};
      String[] fields = new String[]{"custPhone", "custName", "reqMoney", "startTime", "endTime",
          "isBad", "delayDays", "lateFee",  "auditName","overduerName","lastOverdueTime","overdueLogNum"};

      String url = ExportExcel
          .exportExcel(UUIDUtil.getUUID() + ".xls", "坏账列表", titles, fields, datas);
      result.setBody(url);
    }
    return result;
  }

  /**
   * 导出财务日报表
   *
   * @param request
   * @return
   * @throws Exception
   */
  public ResultInfo exportDailyReport(HttpServletRequest request, JSONObject reqJson) throws Exception {
    ResultInfo result = new ResultInfo();
    String rqq = reqJson.getString("rqq");
    String rqz = reqJson.getString("rqz");

    // 拼接检索条件
    StringBuffer where = new StringBuffer("");
    List<Object> params = new ArrayList<Object>();
    if (!CommonUtil.isEmpty(rqq)) {
      where.append(" and a.rq >= ?");
      params.add(rqq);
    }
    if (!CommonUtil.isEmpty(rqz)) {
      rqz = rqz.substring(0, 10) + " 23:59:59";
      where.append(" and a.rq <= ?");
      params.add(rqz);
    }

    // 列表数据SQL: 把最需要审核的放在前面
    StringBuffer sb = new StringBuffer("");
    sb.append("select DATE_FORMAT(a.rq,'%Y-%m-%d') rq,a.loan_num loanNum, convert(ifnull(a.loan_money,0)/100,DECIMAL(15,2)) loanMoney,a.down_num downNum,convert(ifnull(a.down_money,0)/100,DECIMAL(15,2)) downMoney,loan_num2 xjNum,convert(ifnull(loan_money2,0)/100,DECIMAL(15,2)) xjMoney,")
        .append(" convert(ifnull(a.handle_fee,0)/100,DECIMAL(15,2)) handleFee, a.repay_num repayNum,convert(ifnull(a.repay_money,0)/100,DECIMAL(15,2)) repayMoney,convert(ifnull(a.repay_money2,0)/100,DECIMAL(15,2)) repayLatefee,convert(ifnull(a.repay_bj,0)/100,DECIMAL(15,2)) repayBj,convert(ifnull(a.repay_lx,0)/100,DECIMAL(15,2)) repayLx,")
        .append(" a.delay_num delayNum,convert(ifnull(a.delay_money,0)/100,DECIMAL(15,2)) delayMoney,a.bad_num badNum,convert(ifnull(a.bad_money,0)/100,DECIMAL(15,2)) badMoney,(a.agent_money+a.agent_money1)/100 money")
        .append(" from t_count_oper_stats_day a where 1=1")
        .append(where.toString()).append(" order by a.rq desc");

    List<Map<String, Object>> datas = readService.findMapBySql(sb.toString(), params.toArray());
    result.setStatus(0);
    result.setMessage("成功");
    if (CollectionUtils.isNotEmpty(datas)) {
      String[] titles = new String[]{"日期", "货款单数", "货款金额（元）", "续借单数",
          "续借金额（元）", "放款单数", "放款金额（元）", "手续费（元）", "收款单数",
          "收款金额（元）","收款滞纳金（元）","收款本金（元）","收款利息（元）","逾期单数",
          "逾期金额（元）","坏帐单数","坏帐金额（元）","佣金（元）"};
      String[] fields = new String[]{"rq", "loanNum", "loanMoney", "xjNum", "xjMoney",
          "downNum", "downMoney", "handleFee",  "repayNum","repayMoney","repayLatefee",
          "repayBj","repayLx","delayNum","delayMoney","badNum","badMoney","money"};

      String url = ExportExcel
          .exportExcel(UUIDUtil.getUUID() + ".xls", "财务日报表", titles, fields, datas);
      result.setBody(url);
    }
    return result;
  }




}
